Recently I've discovered a quite big perfomance problem when running INFORMATION_SCHEMA queries on mysql.

In particular I found that on some systems (some mysql versions) a query like

can take up to 0.1s to run!

Clearly such time interval is not acceptable, and I think it happens only with some versions of mysql because I've run some tests on my local machine and on the server where this site is running obtaining a result of 0.0007 seconds, we are talking about a difference of two orders of magnitude!

I've not investigated more about which mysql versions are affected and if there are any patches, I'll do it in the next future, but for now I've adopted another solution.

The (current) solution

The problem for me was that Jeff (php framework) performs such query every time a model object is instantiated, in order to get information about the object's fields.

So the solution for me was to cache the query result for each model. I've done this using the powerful cache class which jeff provides, and which is included in the following gist

How to use the cache class

So using this cache class is quite simple. Imagine we have a function called getTableStructure($table) which returns the structure of a table running the query above, all we have to do is to replace

with

And we've done! Basically the code inside the if condition is executed only if the get method of the cache object returns false, and it returns false only if the caching file is not present or the caching time has expired.

Adjust the caching time to fit your needs, I've found that this way the charging time of some pages is really improved, also of values of seconds in some cases!

Considerations

Generally it would be a good practice to cache the result of all complex queries!

The problem here is to set the right expiration time, but even in this case there some cases where caching a query result could be a problem, for example if we update our site contents often, the changes will be available only when the caching time expires.

But do not forget that almost in every project we control the action of insertion and modification of contents in the backend, so actually it is possible to delete the caching files (which are gruped by a group_name) when a modification or an insertion is performed, so that the first time our page is visited a new cache file is generated, and the problem disappears.